require"utils"
require"luasql.sqlite3"
crud = {}
db_mt = {
  private = {
      create = function(self,uri,data)
        return assert(
          self.con:execute(
             string.format([[INSERT OR REPLACE INTO DATA VALUES( '%s' , '%s' );]],uri,data)
             )
          )
      end,
      read = function(self,uri)
        local cur = assert(
          self.con:execute(
             string.format([[SELECT * FROM DATA WHERE uri LIKE '%s'; ]],uri,data)
             )
          )
        local newcur = {_cur = cur}
        newcur.fetch = function(self,...)
          return self._cur:fetch(...)
        end
        newcur.foreach = function(self,f)
          row = self._cur:fetch ({}, "a")
          while row do
            f(row)
            row = self._cur:fetch (row, "a")
          end  
        end
        newcur.close = function(self)
          self._cur:close()
        end
        return newcur  
      end,
      update = function(self,uri,data)
        return assert(
          self.con:execute(
             string.format([[UPDATE DATA SET data = '%s' WHERE uri LIKE '%s'; ]],data,uri)
             )
          )
      end,
      delete = function(self,uri)
        return assert(
          self.con:execute(
             string.format([[DELETE FROM DATA WHERE uri LIKE '%s'; ]],uri)
             )
          )
      end,
      close = function (self)
        self.con:close()
        self.env:close()
      end,
  },
  __index = function (t,k)
    return db_mt.private[k]
  end
}
crud.new = function(dbname)
  local db = {}
  db.env = assert (luasql.sqlite3())
  db.con = assert (db.env:connect(dbname))
  db.con:execute[[CREATE TABLE DATA( uri  varchar(50), data blob(256) )]]
  db.con:execute[[CREATE UNIQUE INDEX IF NOT EXISTS uri ON DATA (uri DESC) ]]
  setmetatable(db,db_mt)
  return db
end 

--[==[--DEMO--
t = crud.new("testdb")
t:create("lala","data")
cur = t:read("lala")
row = cur:fetch ({}, "a")
while row do
  print(string.format("%s:%s", row.uri, row.data))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end

t:update("lala","newdata")
cur = t:read("lala")
row = nil--cur:fetch ({}, "a")
while row do
  print(string.format("%s:%s", row.uri, row.data))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end

cur = t:read("lala")
cur:foreach(function(row) print(row.data) end)
--]==]--

